﻿namespace AnyOffice.DAL
{
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class RmsLendDao
    {
        public void Allow(SqlConnection conn, int lendId, int allow)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Rms_Lend] ");
            builder.Append("SET ");
            builder.Append("\t[Allow] = @Allow ");
            builder.Append("WHERE ");
            builder.Append("\t[LendId] = @LendId ");
            builder.Append("IF @Allow <> 0 ");
            builder.Append("BEGIN ");
            builder.Append("\tUPDATE [dbo].[Rms_Lend] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[AllowTime] = GETDATE() ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[LendId] = @LendId ");
            builder.Append("END ");
            builder.Append("ELSE ");
            builder.Append("BEGIN ");
            builder.Append("\tUPDATE [dbo].[Rms_Lend] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[AllowTime] = NULL ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[LendId] = @LendId ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@LendId", SqlDbType.Int);
            commandParameters[0].Value = lendId;
            commandParameters[1] = new SqlParameter("@Allow", SqlDbType.Int);
            commandParameters[1].Value = allow;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
        }

        public bool Delete(SqlConnection conn, int lendId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Rms_Lend] ");
            builder.Append("WHERE ");
            builder.Append("\t[LendId] = @LendId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@LendId", SqlDbType.Int) };
            commandParameters[0].Value = lendId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool DeleteMy(SqlConnection conn, int lendId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Rms_Lend] ");
            builder.Append("WHERE ");
            builder.Append("\t[LendId] = @LendId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@LendId", SqlDbType.Int);
            commandParameters[0].Value = lendId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool IsLend(SqlConnection conn, int fileId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF EXISTS(SELECT [FileId] FROM [dbo].[Rms_Lend] WHERE [FileId] = @FileId AND [UserId] = @UserId AND [Allow] = 1) ");
            builder.Append("\tSELECT 1 ");
            builder.Append("ELSE ");
            builder.Append("\tSELECT 0 ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FileId", SqlDbType.Int);
            commandParameters[0].Value = fileId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public void Lend(SqlConnection conn, int fileId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Rms_Lend] ");
            builder.Append("( ");
            builder.Append("\t[FileId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[LendTime] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@FileId, ");
            builder.Append("\t@UserId, ");
            builder.Append("\tGETDATE() ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@FileId", SqlDbType.Int);
            commandParameters[0].Value = fileId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
        }

        public void Return(SqlConnection conn, int lendId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Rms_Lend] ");
            builder.Append("SET ");
            builder.Append("\t[ReturnTime] = GETDATE(), ");
            builder.Append("\t[Allow] = 3 ");
            builder.Append("WHERE ");
            builder.Append("\t[LendId] = @LendId ");
            builder.Append("\tAND [Allow] = 1 ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@LendId", SqlDbType.Int);
            commandParameters[0].Value = lendId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
        }

        public DataTable SelectMyNo(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("\tAND [Allow] = 2 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[LendId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectMyOk(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("\tAND [Allow] = 1 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[LendId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectMyReturn(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("\tAND [Allow] = 3 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[LendId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectMyWait(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [UserId] = @UserId ");
            builder.Append("\tAND [Allow] = 0 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[LendId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectNo(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [Rms_Lend].[Allow] = 2 ");
            builder.Append("\tAND [Rms_Lend].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Rms_Lend].[LendId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectOk(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [Rms_Lend].[Allow] = 1 ");
            builder.Append("\tAND [Rms_Lend].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Rms_Lend].[LendId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectReturn(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [Rms_Lend].[Allow] = 3 ");
            builder.Append("\tAND [Rms_Lend].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Rms_Lend].[LendId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }

        public DataTable SelectWait(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Lend].*, ");
            builder.Append("\t[Rms_File].[FileCode], ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Lend], ");
            builder.Append("\t[dbo].[Rms_File], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Lend].[FileId] = [Rms_File].[FileId] ");
            builder.Append("\tAND [Rms_Lend].[Allow] = 0 ");
            builder.Append("\tAND [Rms_Lend].[UserId] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Rms_Lend].[LendId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Rms_Lend";
            return set.Tables[0];
        }
    }
}

